﻿/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MixNP.DatabaseLayer.Alerts
{
    public static class AlertMe
    {
        public static DataTable GetAlert(int categoryId, short cityId, decimal price)
        {
            string sql = @"SELECT * FROM AlertMe
                        WHERE CategoryId=@CategoryId
                        AND @Price BETWEEN PriceFrom AND PriceTo
                        AND COALESCE(CityId, 0) IN(CAST(@CityId AS varchar), 0)"; 

            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);
                command.Parameters.AddWithValue("@CityId", cityId);
                command.Parameters.AddWithValue("@Price", price);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }
        
        public static bool SaveAlert(string emailAddress, int categoryId, short cityId, decimal priceFrom, decimal priceTo)
        {
            string sql = "INSERT INTO dbo.AlertMe(EmailAddress, CategoryId, CityId, PriceFrom, PriceTo) SELECT @EmailAddress, @CategoryId, @CityId, @PriceFrom, @PriceTo;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);

                if (categoryId.Equals(int.MinValue) || categoryId.Equals(0))
                {
                    command.Parameters.AddWithValue("@CategoryId", DBNull.Value);
                }
                else
                {
                    command.Parameters.AddWithValue("@CategoryId", categoryId);
                }

                if (cityId.Equals(short.MinValue) || cityId.Equals(0))
                {
                    command.Parameters.AddWithValue("@CityId", DBNull.Value);
                }
                else
                {
                    command.Parameters.AddWithValue("@CityId", cityId);
                }

                command.Parameters.AddWithValue("@PriceFrom", priceFrom);
                command.Parameters.AddWithValue("@PriceTo", priceTo);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }
        public static bool DeleteAlert(string emailAddress, int categoryId)
        {
            string sql = "DELETE FROM dbo.AlertMe WHERE EmailAddress=@EmailAddress AND CategoryId=@CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        }
    }
}
